﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;
using System.Collections;

namespace Agresso.Interface.Layer.Service
{
    public class BunkerInventoryManagerMappingService
    {
        DataBaseConnectivity m_dataBaseConectivity;

        public BunkerInventoryManagerMappingService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public BunkerInventoryManagerMapping Insert(BunkerInventoryManagerMapping map)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = BunkerInventoryManagerMappingSQL.Insert();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = map.profit_centre_fullname.Trim();
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = map.client.Trim();
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = map.description.Trim();
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = map.dim_value.Trim();
                cmd.Parameters.Add("@rel_value", SqlDbType.NVarChar).Value = map.rel_value.Trim();
                cmd.Parameters.Add("@client_map", SqlDbType.NVarChar).Value = map.client_map.Trim();
                cmd.Parameters.Add("@description_map", SqlDbType.NVarChar).Value = map.description_map.Trim();
                cmd.Parameters.Add("@dim_value_map", SqlDbType.NVarChar).Value = map.dim_value_map.Trim();
                cmd.Parameters.Add("@rel_value_map", SqlDbType.NVarChar).Value = map.rel_value_map.Trim();
                cmd.ExecuteNonQuery();
                cmd.CommandText = BunkerInventoryManagerMappingSQL.MaxID();
                cmd.Parameters.Clear();
                map.BunkerInventoryManagerMappingID = Convert.ToInt32(cmd.ExecuteScalar());
                return map;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void Update(BunkerInventoryManagerMapping map)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = BunkerInventoryManagerMappingSQL.Update();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = map.profit_centre_fullname.Trim();
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = map.client.Trim();
                cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = map.description.Trim();
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = map.dim_value.Trim();
                cmd.Parameters.Add("@rel_value", SqlDbType.NVarChar).Value = map.rel_value.Trim();
                cmd.Parameters.Add("@client_map", SqlDbType.NVarChar).Value = map.client_map.Trim();
                cmd.Parameters.Add("@description_map", SqlDbType.NVarChar).Value = map.description_map.Trim();
                cmd.Parameters.Add("@dim_value_map", SqlDbType.NVarChar).Value = map.dim_value_map.Trim();
                cmd.Parameters.Add("@rel_value_map", SqlDbType.NVarChar).Value = map.rel_value_map.Trim();
                cmd.Parameters.Add("@BunkerInventoryManagerMappingID", SqlDbType.Int).Value = map.BunkerInventoryManagerMappingID;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public BunkerInventoryManagerMapping SearchByProfitCentre(string profitCentre)
        {
            BunkerInventoryManagerMapping result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = BunkerInventoryManagerMappingSQL.FIndByProfitCentre();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = profitCentre.Trim();
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    result = new BunkerInventoryManagerMapping(rd);
                    rd.Close();
                }
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public BunkerInventoryManagerMapping SearchByDimValue(string dimVal)
        {
            BunkerInventoryManagerMapping result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = BunkerInventoryManagerMappingSQL.FindByDimValue();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Add("@dim_value", SqlDbType.NVarChar).Value = dimVal.Trim();
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    result = new BunkerInventoryManagerMapping(rd);
                    rd.Close();
                }
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public BunkerInventoryManagerMapping SearchInsert(string profitCentre, string client, string description, string dimval, string relval)
        {
            BunkerInventoryManagerMapping result = SearchByProfitCentre(profitCentre);
            if (result == null)
            {
                result = new BunkerInventoryManagerMapping();
                result.profit_centre_fullname = profitCentre;
                result.client = client;
                result.description = description;
                result.dim_value = dimval;
                result.rel_value = relval;
                result = Insert(result);
            }
            return result;
        }

        public IList GetAllMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = BunkerInventoryManagerMappingSQL.GetAllMapping();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new BunkerInventoryManagerMapping(rd));
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
